package cn.itlaobing.dao;

import cn.itlaobing.model.AreasModel;
import cn.itlaobing.model.GoodDetailModel;
import cn.itlaobing.model.GoodsDetailModel;
import cn.itlaobing.model.OffLineShopsModel;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2017-11-13.
 */
public class OrderDetailsDao extends BaseDao {
    /*
    * 该方法负责查询当前index 界面的信息，查询热销前六条*/
    public List<GoodDetailModel> goodDetails() throws SQLException
    {

        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = " SELECT goods.id goodId,t.good_name goodName,pic.picUrl goodCoverImg,goods.price salePrice " +
                "FROM itlb_goods goods " +
                "LEFT JOIN (SELECT good_id,good_name,SUM(buy_count) num " +
                "FROM itlb_order_details " +
                "GROUP BY good_name ORDER BY buy_count DESC)t " +
                "ON goods.id=t.good_id " +
                "INNER JOIN itlb_goods_pic pic " +
                "ON t.good_id=pic.goodsId " +
                "AND goods.status_line=1 " +

                "LIMIT 6";

        BeanListHandler<GoodDetailModel> beanListHandler = new BeanListHandler<GoodDetailModel>(GoodDetailModel.class);
        List<GoodDetailModel> list = qr.query(sql, beanListHandler);
        return list;
    }
//得到新上架商品的详细信息
    public List<GoodDetailModel> firstSale() throws SQLException
    {

        QueryRunner qr = new QueryRunner(getDataSource());
        String sql = "SELECT good.id goodId, good.good_name goodName,pic.picUrl goodCoverImg,good.price salePrice FROM itlb_goods good INNER JOIN itlb_goods_pic pic" +
                " ON good.id=pic.goodsId" +
                " AND status_line=1 limit 6";
        BeanListHandler<GoodDetailModel> beanListHandler = new BeanListHandler<GoodDetailModel>(GoodDetailModel.class);
        List<GoodDetailModel> list = qr.query(sql, beanListHandler);
        return list;
    }
    //得到当前的大图信息，
    public List<String> focussImg() throws SQLException {
        QueryRunner qr=new QueryRunner(getDataSource());
        String sql="SELECT img FROM itlb_focuss";
        /*MapHandler mapHandler=new MapHandler();*/
        List<String> list=qr.query(sql, new ResultSetHandler<List<String>>() {
            @Override
            public List<String> handle(ResultSet rs) throws SQLException {
                List<String> list=new ArrayList<String>();
                while(rs.next())
                {
                    list.add(rs.getString(1));
                }
                return list;
            }
        });
        /*System.out.println(map.size());
        for(Map.Entry<String,Object> m:map.entrySet())
        {
            list.add((String)m.getValue());
        }*/
        return list;
    }
    //查询到所有的大图信息
    public List<String> detailImg(Integer id) throws SQLException {
        QueryRunner qr=new QueryRunner(getDataSource());
        String sql="SELECT picUrl FROM itlb_goods_pic WHERE goodsId=?";
        /*MapHandler mapHandler=new MapHandler();*/
        List<String> list=qr.query(sql, new ResultSetHandler<List<String>>() {
            @Override
            public List<String> handle(ResultSet rs) throws SQLException {
                List<String> list=new ArrayList<String>();
                while(rs.next())
                {
                    list.add(rs.getString(1));
                }
                return list;
            }
        },id);

        return list;
    }
    //查找根据id得到当前商品的全部信息
    public GoodsDetailModel  queryDetailByid(Integer id) throws SQLException {
        QueryRunner qr=new QueryRunner(getDataSource());
        String sql="SELECT * FROM itlb_goods WHERE id=?";
        BeanHandler<GoodsDetailModel> beanHandler=new BeanHandler<GoodsDetailModel>(GoodsDetailModel.class);
        return qr.query(sql,beanHandler,id);
    }
    //查询全部的街道信息
    public List<AreasModel> queryAll() throws SQLException {
        QueryRunner qr =new QueryRunner(getDataSource());
        String sql="SELECT * FROM  itlb_areas";
        BeanListHandler<AreasModel> beanListHandler=new BeanListHandler<AreasModel>(AreasModel.class);
        List<AreasModel> list=qr.query(sql,beanListHandler);
        return list;
    }
    //查询全部的线上商店信息
    public List<OffLineShopsModel> queryByid(Integer id) throws SQLException {
        QueryRunner qr=new QueryRunner(getDataSource());
        String sql="SELECT * FROM itlb_offline_shops WHERE area_id=?";
        BeanListHandler<OffLineShopsModel> beanListHandler=new BeanListHandler<OffLineShopsModel>(OffLineShopsModel.class);
        List<OffLineShopsModel> list=qr.query(sql,beanListHandler,id);
        return list;
    }


}



